﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Remind.BLL;
//导入空间
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using System.Data.OleDb;
using System.Configuration;

namespace Remind.View
{
    public partial class FrmImport : Form
    {
        //分组业务层对象
        private readonly GroupBLL gBLL = new GroupBLL();
        //
        private readonly ContactsBLL cBLL = new ContactsBLL();

        //excel工作簿对象
        HSSFWorkbook hssfworkbook;

        private readonly string strConn = ConfigurationManager.ConnectionStrings["Contacts"].ConnectionString + ConfigurationManager.AppSettings["database"] + "';Jet OLEDB:Database Password=283713763@qq.com;";

        public FrmImport()
        {
            InitializeComponent();
        }

        private void FrmImport_Load(object sender, EventArgs e)
        {
            //绑定分组信息
            cmbGroup.DataSource = gBLL.GetGroup();
            cmbGroup.ValueMember = "Gid";
            cmbGroup.DisplayMember = "GName";
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            //判断是否有分组
            if (cmbGroup.Items.Count <= 0)
            {
                MessageBox.Show("导入的联系人需要分组,请在主面板添加分组之后导入联系人!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            //打开
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "导入联系人信息";
            ofd.DefaultExt = "xls";
            ofd.Filter = "Excel文件 (*.xls)|*.xls";
            ofd.Multiselect = false;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                //获取文件名称
                string fileName = ofd.FileName;
                //excel对象
                InitializeWorkbook(fileName);
                //
                ConvertToDataTable();
            }
        }

        private void ConvertToDataTable()
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            for (int j = 0; j < 18; j++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
            }
            int k = 0;
            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    //排除第一行
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                if (k > 0)
                {
                    dt.Rows.Add(dr);
                }
                k++;
            }
            string gid = cmbGroup.SelectedValue.ToString();
            string sqlContact;
            string sqlTel;
            string sqlAddress;
            string sqlNetWork;
            //联系人值
            string contactvalue = "";
            //电话值
            string telvalue = "";
            //地址值
            string addressvalue = "";
            //网络值
            string networkvalue = "";
            //设置最大值
            if (dt.Rows.Count > 0)
            {
                toolStripProgressBar.Maximum = dt.Rows.Count;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //联系人表sql语句
                    sqlContact = "insert into contacts(gid,cface,cgn,cname,cbirthday,csex,IDcard,cBz)values(" + gid + ",1,1";
                    //电话sql语句
                    sqlTel = "INSERT INTO tel (phone, phone1, family, company, fax,cid) VALUES(";
                    //地址sql语句
                    sqlAddress = "INSERT INTO address (family, company, other,cid) VALUES(";
                    //网络信息sql语句
                    sqlNetWork = "INSERT INTO network (email, qq, msn, blog, website,cid) VALUES (";
                    #region 字符串拼接
                    for (int j = 0; j < 5; j++)
                    {
                        if (dt.Rows[i][j] == null)
                        {
                            contactvalue += ",' '";
                        }
                        else
                        {
                            if (j == 2)
                            {
                                if (dt.Rows[i][j].ToString().Trim() == "男")
                                    contactvalue += ",'1'";
                                else
                                    contactvalue += ",'0'";
                            }
                            else
                            {
                                contactvalue += ",'" + dt.Rows[i][j].ToString().Trim() + "'";
                            }
                        }
                    }
                    for (int l = 5; l < 10; l++)
                    {
                        if (dt.Rows[i][l] == null)
                        {
                            telvalue += "' ',";
                        }
                        else
                        {
                            telvalue += "'" + dt.Rows[i][l].ToString().Trim() + "',";
                        }
                    }
                    for (int m = 10; m < 13; m++)
                    {
                        if (dt.Rows[i][m] == null)
                        {
                            addressvalue += "' ',";
                        }
                        else
                        {
                            addressvalue += "'" + dt.Rows[i][m].ToString().Trim() + "',";
                        }
                    }
                    for (int n = 13; n < 18; n++)
                    {
                        if (dt.Rows[i][n] == null)
                        {
                            networkvalue += "' ',";
                        }
                        else
                        {
                            networkvalue += "'" + dt.Rows[i][n].ToString().Trim() + "',";
                        }
                    }
                    #endregion
                    using (OleDbConnection connection = new OleDbConnection(strConn))
                    {
                        try
                        {
                            connection.Open();
                            //最后拼接的sql语句
                            sqlContact += contactvalue + ")";
                            OleDbCommand command = new OleDbCommand(sqlContact, connection);
                            command.ExecuteNonQuery();
                            command.Dispose();
                            sqlContact = "";
                            contactvalue = "";
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("数据导入失败,请按照模板格式录入正确格式!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                            break;
                        }
                    }
                    int cid = cBLL.GetMaxCid();
                    using (OleDbConnection connection = new OleDbConnection(strConn))
                    {
                        try
                        {
                            connection.Open();
                            sqlTel += telvalue + cid + ")";
                            OleDbCommand telcmd = new OleDbCommand(sqlTel, connection);
                            telcmd.ExecuteNonQuery();
                            telcmd.Dispose();
                            sqlTel = "";
                            telvalue = "";
                            sqlAddress += addressvalue + cid + ")";
                            OleDbCommand addrcmd = new OleDbCommand(sqlAddress, connection);
                            addrcmd.ExecuteNonQuery();
                            addrcmd.Dispose();
                            sqlAddress = "";
                            addressvalue = "";
                            sqlNetWork += networkvalue + cid + ")";
                            OleDbCommand networkcmd = new OleDbCommand(sqlNetWork, connection);
                            networkcmd.ExecuteNonQuery();
                            networkcmd.Dispose();
                            sqlNetWork = "";
                            networkvalue = "";
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show(e.Message);
                            MessageBox.Show("数据导入失败,请按照模板格式录入正确格式!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                    toolStripProgressBar.Value = i + 1;
                }
                if (toolStripProgressBar.Value < dt.Rows.Count)
                {
                    toolStripStatusLabel2.Text = "导入" + toolStripProgressBar.Value + "条数据!";
                }
                else
                {
                    toolStripStatusLabel2.Text = "导入成功!";
                }
                toolStripProgressBar.Value = 0;
            }
        }
        private void InitializeWorkbook(string path)
        {
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }
    }
}
